﻿using Dapper;
using ThinkNet.ORM.AiExpression;
using ThinkNet.ORM.Config;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Transactions;

namespace ThinkNet.ORM.DataBase
{
    public class SqlServer : IDataBase
    {
        #region 新增
        public override int Insert<T>(T t, bool IsReturnId = false)
        {
            t.SetInsertSysCols();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerInsert<T>(t, tableName, IsReturnId);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                if (!IsReturnId)
                    return conn.Execute(sqlData.sql, sqlData.param);
                else
                {
                    sqlData.param.Add("@ORMAutoID", dbType: DbType.Int32, direction: ParameterDirection.Output);

                    conn.Execute(sqlData.sql + ";SELECT @ORMAutoID=SCOPE_IDENTITY();", sqlData.param);

                    return sqlData.param.Get<int>("@ORMAutoID");
                }
            }
        }

        public override long Insert64<T>(T t, bool IsReturnId = false)
        {
            t.SetInsertSysCols();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerInsert<T>(t, tableName, IsReturnId);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                if (!IsReturnId)
                    return conn.Execute(sqlData.sql, sqlData.param);
                else
                {
                    sqlData.param.Add("@ORMAutoID", dbType: DbType.Int64, direction: ParameterDirection.Output);

                    conn.Execute(sqlData.sql + ";SELECT @ORMAutoID=SCOPE_IDENTITY();", sqlData.param);

                    return sqlData.param.Get<long>("@ORMAutoID");
                }
            }
        }

        public override int InsertBat<T>(List<T> list)
        {
            foreach (var t in list)
            {
                t.SetInsertSysCols();
            }

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerInsert<T>(list.FirstOrDefault(), tableName);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, list);
            }
        }
        #endregion

        #region 更新
        public override int Update<T>(T t)
        {
            t.SetUpdateSysCols();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerUpdate<T>(t, tableName);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, sqlData.param);
            }
        }

        public override int Update<T>(T t, Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp)
        {
            t.SetUpdateSysCols();

            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerUpdate<T>(t, tableName, where);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, sqlData.param);
            }
        }

        public override int UpdateBat<T>(List<T> list)
        {
            foreach (var t in list)
            {
                t.SetUpdateSysCols();
            }

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerUpdate<T>(list.FirstOrDefault(), tableName);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, list);
            }
        }
        #endregion

        #region 删除
        public override int LogicalDelete<T>(T t)
        {
            t.SetDeleteSysCols();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerUpdate<T>(t, tableName);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, sqlData.param);
            }
        }

        public override int LogicalDelete<T>(T t, Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp)
        {
            t.SetDeleteSysCols();

            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerUpdate<T>(t, tableName, where);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, sqlData.param);
            }
        }

        public override int Delete<T>(T t)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sqlData = SqlMapping.SqlServerDelete<T>(t, tableName);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sqlData.sql, sqlData.param);
            }
        }

        public override int Delete<T>(T t, Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerDelete<T>(t, tableName, where);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Execute(sql);
            }
        }

        #endregion

        #region 实体查询
        public override T Get<T>(string id, string showCols = "*", bool nolock = true)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGet<T>(tableName, id, showCols, nolock);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<T>(sql).FirstOrDefault();
            }
        }

        public override NT Get<T, NT>(string id, string showCols = "*", bool nolock = true)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGet<T>(tableName, id, showCols, nolock);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<NT>(sql).FirstOrDefault();
            }
        }
        #endregion

        #region 列表查询
        public override IList<T> Get<T>(Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp, string showCols = "*", bool nolock = true)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var orderBy = expc.OrderBy();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, showCols);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<T>(sql).ToList();
            }
        }

        public override IList<NT> Get<T, NT>(Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp, string showCols = "*", bool nolock = true)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var orderBy = expc.OrderBy();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, showCols);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<NT>(sql).ToList();
            }
        }

        public override IList<T> GetWhere<T>(string where, string orderBy, string showCols = "*", object param = null, bool nolock = true)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, showCols);

            param = SqlMapping.XParameters(param);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<T>(sql, param).ToList();
            }
        }

        public override IList<NT> GetWhere<T, NT>(string where, string orderBy, string showCols = "*", object param = null, bool nolock = true)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, showCols);

            param = SqlMapping.XParameters(param);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<NT>(sql, param).ToList();
            }
        }
        #endregion

        #region 分页查询
        public override MPageData<T> Get<T>(Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp, int pageIndex, int pageSize, string showCols = "*", bool nolock = true, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var orderBy = expc.OrderBy();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            string sqlCount = string.Empty;

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, pageIndex, out sqlCount, pageSize, showCols, nolock, sqlVersion);

            MPageData<T> m = new MPageData<T>();

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                m.total = conn.Query<int>(sqlCount).SingleOrDefault();

                m.rows = conn.Query<T>(sql).ToList();
            }

            return m;
        }

        public override MPageData<NT> Get<T, NT>(Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp, int pageIndex, int pageSize, string showCols = "*", bool nolock = true, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var orderBy = expc.OrderBy();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            string sqlCount = string.Empty;

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, pageIndex, out sqlCount, pageSize, showCols, nolock, sqlVersion);

            MPageData<NT> m = new MPageData<NT>();

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                m.total = conn.Query<int>(sqlCount).SingleOrDefault();

                m.rows = conn.Query<NT>(sql).ToList();
            }

            return m;
        }

        public override MPageData<T> GetWhere<T>(string where, string orderBy, int pageIndex, int pageSize, string showCols = "*", object param = null, bool nolock = true, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            string sqlCount = string.Empty;

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, pageIndex, out sqlCount, pageSize, showCols, nolock, sqlVersion);

            param = SqlMapping.XParameters(param);

            MPageData<T> m = new MPageData<T>();

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                m.total = conn.Query<int>(sqlCount, param).SingleOrDefault();

                m.rows = conn.Query<T>(sql, param).ToList();
            }

            return m;
        }

        public override MPageData<NT> GetWhere<T, NT>(string where, string orderBy, int pageIndex, int pageSize, string showCols = "*", object param = null, bool nolock = true, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            string sqlCount = string.Empty;

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, pageIndex, out sqlCount, pageSize, showCols, nolock, sqlVersion);

            param = SqlMapping.XParameters(param);

            MPageData<NT> m = new MPageData<NT>();

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                m.total = conn.Query<int>(sqlCount, param).SingleOrDefault();

                m.rows = conn.Query<NT>(sql, param).ToList();
            }

            return m;
        }
        #endregion

        #region Sql查询
        public override IList<T> RunProc<T>(string procName, object param = null)
        {
            param = SqlMapping.XParameters(param);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<T>(procName, param, null, true, null, CommandType.StoredProcedure).ToList();
            }
        }

        public override IList<T> RunSql<T>(string sql, object param = null)
        {
            param = SqlMapping.XParameters(param);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<T>(sql, param).ToList();
            }
        }

        public override MPageData<T> RunSql<T>(string sql, string orderBy, int pageIndex, int pageSize, object param = null, ESqlVersion sqlVersion = ESqlVersion.SqlServer2012)
        {
            string sqlCount = string.Empty;

            var sqlData = SqlMapping.ExecuteSqlPage(sql, orderBy, pageIndex, out sqlCount, pageSize, sqlVersion);

            param = SqlMapping.XParameters(param);

            MPageData<T> m = new MPageData<T>();

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                m.total = conn.Query<int>(sqlCount, param).SingleOrDefault();

                m.rows = conn.Query<T>(sqlData, param).ToList();
            }

            return m;
        }
        #endregion

        #region 统计
        public override decimal Sum<T>(Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp, string SumCol, bool nolock = true)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var orderBy = expc.OrderBy();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, string.Format("ISNULL(SUM({0}),0)", SumCol), nolock);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<decimal>(sql).SingleOrDefault();
            }
        }

        public override int Count<T>(Expression<Func<IQueryable<T>, IQueryable<T>>> bizExp, bool nolock = true)
        {
            AiExpConditions<T> expc = new AiExpConditions<T>();

            expc.Add(bizExp);

            var where = expc.Where();

            var orderBy = expc.OrderBy();

            var typeString = typeof(T).ToString();

            var tableName = SqlMapping.GetTableName(typeString);

            var sql = SqlMapping.SqlServerGetList<T>(tableName, where, orderBy, "COUNT(1)", nolock);

            using (IDbConnection conn = SqlMapping.GetConnection(BaseConfig.DefaultDBKey))
            {
                return conn.Query<int>(sql).SingleOrDefault();
            }
        }
        #endregion

        #region 事务
        public override TransactionScope TransMaster(System.Transactions.IsolationLevel isolationLevel = System.Transactions.IsolationLevel.Serializable)
        {
            TransactionOptions option = new TransactionOptions();

            option.IsolationLevel = isolationLevel;

            return new TransactionScope(TransactionScopeOption.Required, option);
        }
        #endregion
    }
}
